<?php
namespace Lib\db;

use Lib\Config;

abstract class Database_Object{
    public $DB_Name;
    public $DB_Open;
    public $DB_Conn;

    /**
     * Database_Object constructor.
     * @param $database
     * @param $hostname
     * @param $hostport
     * @param $username
     * @param $password
     */
    public function __construct($database, $hostname, $hostport, $username, $password){
        $this->DB_Name = $database;
        $this->DB_Conn = mysqli_connect($hostname . ":" . $hostport, $username, $password);
        if(!$this->DB_Conn) { die('Critical Stop Error: Database Error<br />' . mysqli_connect_error() ); }
        mysqli_select_db($this->DB_Conn,$this->DB_Name);
        mysqli_query($this->DB_Conn,"set character set 'utf8'");
        mysqli_query($this->DB_Conn,"set names 'utf8'");
    }

    public function __clone() {}

    public function __destruct(){
        if(!is_object($this->DB_Conn)){\mysqli_close($this->DB_Conn);}  // commented out due to current shared-link close 'feature'.  If left in, causes a warning that this is not a valid link resource.
    }
}

final class MySql extends Database_Object{
    /**
     * @var string
     */
    private $lastSql = '';
    protected static $_conf = [
        'DATABASE'=>'information_schema',//需要操作的数据库
        'HOSTNAME'=>'127.0.0.1',//主机地址
        'HOSTPORT'=>'3306',//mysql主机端口
        'USERNAME'=>'root',//用户名
        'PASSWORD'=>'root',//密码


    ];

    /**
     * 使用指定的连接配置连接MySQL数据库
     * @param string $confName
     * @return MySql
     */
    public static function Open($confName = 'MYSQL'){
        $_conf = Config::ini($confName,self::$_conf);
        return new self($_conf['DATABASE'],$_conf['HOSTNAME'], $_conf['HOSTPORT'], $_conf['USERNAME'], $_conf['PASSWORD']);
    }

    /**
     * @param $sql
     * @param int $return_format
     * @return array|bool|int|\mysqli_result|null|string
     * @throws \Exception
     */
    public function query($sql, $return_format = 0){
        $query = mysqli_query($this->DB_Conn,$sql);// OR die(mysql_error());
        if($query === NULL){
            throw new \Exception(mysqli_errno($this->DB_Conn).':'.mysqli_error($this->DB_Conn),mysqli_errno($this->DB_Conn));
        }
        if(empty($query)){
            return [];
        }
        switch ($return_format){
            case 1:
                $query = mysqli_fetch_row($query);
                return $query;
                break;
            case 2:
                $query = mysqli_fetch_array($query);
                return $query;
                break;
            case 3:
                $query = mysqli_insert_id($this->DB_Conn);
                return $query;
            case 4:
                return mysqli_affected_rows($this->DB_Conn);
            default:
                $r = array();
                while($row = mysqli_fetch_assoc($query)){
                    $r[]   =   $row;
                }
                return $r;
        }
    }

    /**
     * @return string
     */
    public function getLastSql(){
        return $this->lastSql;
    }

    /**
     * @param $data
     * @param $table
     * @param bool $replace
     * @return array|bool|int|\mysqli_result|null|string
     * @throws \Exception
     */
    public function insert($data, $table, $replace=false) {
        $values  =  $fields    = array();
        foreach ($data as $key=>$val){
            $value   =  $this->parseValue($val);
            if(is_scalar($value)) { // 过滤非标量数据
                $values[]   =  $value;
                $fields[]     =  $this->parseKey($key);
            }
        }
        $sql   =  ($replace?'REPLACE':'INSERT').' INTO '.$table.' ('.implode(',', $fields).') VALUES ('.implode(',', $values).')';
        $this->lastSql = $sql;
        return $this->query($sql,3);
    }

    /**
     * @param $data
     * @param $table
     * @param $where
     * @param bool $replace
     * @return array|bool|int|\mysqli_result|null|string
     * @throws \Exception
     */
    public function update($data, $table, $where, $replace=false) {
        $values  =  $fields    = array();
        $setstr ="";
        foreach ($data as $key=>$val){
            $value   =  $this->parseValue($val);
            if(is_scalar($value)) { // 过滤非标量数据
                $values[]   =  $value;
                $fields[]     =  $this->parseKey($key);
                $setstr .= $this->parseKey($key)." = ".$value.",";
            }
        }
        $sql   =  ($replace?'REPLACE':'update ').$table.' set '.substr($setstr, 0, -1).' where '.$where;
        //echo $sql;
        $this->lastSql = $sql;
        return $this->query($sql,4);
    }

    /**
     * @param $datas
     * @param $table
     * @param bool $replace
     * @param bool $IGNORE
     * @return array|bool|int|\mysqli_result|null|string
     * @throws \Exception
     */
    public function insertAll($datas, $table, $replace=false, $IGNORE=false) {
        if(!is_array($datas[0])) return false;
        $fields = array_keys($datas[0]);
        array_walk($fields, array($this, 'parseKey'));
        $values  =  array();
        foreach ($datas as $data){
            $value   =  array();
            foreach ($data as $key=>$val){
                $val   =  $this->parseValue($val);
                if(is_scalar($val)) { // 过滤非标量数据
                    $value[]   =  $val;
                }
            }
            $values[]    = '('.implode(',', $value).')';
        }
        $sql   =  ($replace?'REPLACE':'INSERT').($IGNORE?' IGNORE ':'').' INTO '.$table.' ('.implode(',', $fields).') VALUES '.implode(',',$values);
        $this->lastSql = $sql;
        return $this->query($sql,4);
    }

    /**
     * 字段和表名处理添加`
     * @access protected
     * @param string $key
     * @return string
     */
    protected function parseKey(&$key) {
        $key   =  trim($key);
        if(!preg_match('/[,\'\"\*\(\)`.\s]/',$key)) {
            $key = '`'.$key.'`';
        }
        return $key;
    }

    /**
     * value分析
     * @access protected
     * @param mixed $value
     * @return string
     */
    protected function parseValue($value) {
        if(is_string($value)) {
            $value =  '\''.$this->escapeString($value).'\'';
        }elseif(isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp'){
            $value =  $this->escapeString($value[1]);
        }elseif(is_array($value)) {
            $value =  array_map(array($this, 'parseValue'),$value);
        }elseif(is_bool($value)){
            $value =  $value ? '1' : '0';
        }elseif(is_null($value)){
            $value =  'null';
        }
        return $value;
    }

    /**
     * SQL指令安全过滤
     * @access public
     * @param string $str  SQL字符串
     * @return string
     */
    public function escapeString($str) {
        if($this->DB_Open) {
            return mysqli_real_escape_string($this->DB_Open,$str);
        }else{
            return $str;
        }
    }
}